﻿using System;
using System.Data;
using System.Text;
using System.Globalization;

using BizElements.Core;
using BizElements.Core.ProviderComponents;

namespace BizElements.Core.PostgreSql
{
    sealed class PostgreSqlInserter : IInserter
    {
        // Currently, only SERIAL column data type/macro is supported. Stand-alone sequences are currently not supported.

        #region RenderInsert.

        /// <summary>
        /// Renders INSERT statement and code that retrieves the new ID.
        /// </summary>
        /// <param name="insert">INSERT statement that is being rendered.</param>
        /// <param name="nextSequence">Ignored. SQL Server doesn't use sequences.</param>
        /// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
        /// <param name="output">StringBuilder to which the SQL code is appended.</param>
        /// <param name="parameters">SQL parameter collection to which the object's and its children's
        /// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
        /// <returns><b>null</b> because automatically generated ID is fetched via RETURNING clauseas a part of INSERT statement.</returns>
        public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            // Renders INSERT statements for DBMSs that support an auto-identity fields.
            // Auto-id field may or may not be in the column-value list.
            // If auto-incremented field is in the column-value list it will be skipped.
            // Table may have only one auto-identity field.
            // Method expects that all errors have been identified and processed in the caller.
            // Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.			
            int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);

            AppendInsertIntoTableName(insert, dbms, output);
            if (numberOfFieldsToRender > 0)
            {
                AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
                AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
            }
            else
            {
                AppendDefaultValuesExpression(output);
            }
            
            IDbColumn autoIdField = GetAutoIdField(insert.Table);
            if (autoIdField != null)
            {
                // RETURNING id
                output.Append(" RETURNING ");
                autoIdField.RenderColumnName(dbms, output);
            }

            // Return auto-id DB parameter. Callers require it to retrieve the new ID value.
            DbParameter autoId = null;
            return autoId;
        }

        private static bool IsAutoIdFieldInColumnValueList(InsertStatement insert)
        {
            foreach (InsertExpression colValPair in insert.ColumnsAndValues)
            {
                if (colValPair.Column.AutoIncrement)
                    return true;
            }

            return false;
        }

        private static int GetTotalNumberOfFieldsToRender(InsertStatement insert)
        {
            bool autoIdFieldIsInColumnValueList = IsAutoIdFieldInColumnValueList(insert);
            int numberOfFieldsToRender = (autoIdFieldIsInColumnValueList) ? insert.ColumnsAndValues.Count - 1 : insert.ColumnsAndValues.Count;
            return numberOfFieldsToRender;
        }

        private static bool HasAutoIdField(IDbTable table)
        {
            foreach (IDbColumn field in table.Columns)
            {
                if (field.AutoIncrement)
                    return true;
            }

            return false;
        }

        private static IDbColumn GetAutoIdField(IDbTable table)
        {
            foreach (IDbColumn field in table.Columns)
            {
                if (field.AutoIncrement)
                    return field;
            }

            return null;
        }

        private static void AppendInsertIntoTableName(InsertStatement insert, DbmsType dbms, StringBuilder output)
        {
            output.Append("INSERT INTO ");
            insert.Table.RenderTableName(dbms, output);
        }

        private static void AppendDefaultValuesExpression(StringBuilder output)
        {
            // SQL Server syntax when table contains only a single auto-id field.
            // INSERT INTO <TABLE_NAME> DEFAULT VALUES.
            output.Append(" DEFAULT VALUES");
        }

        private static void AppendBracketsWithAllFieldsExceptAutoId(InsertStatement insert, DbmsType dbms, StringBuilder output, int numberOfFieldsToRender)
        {
            output.Append(" (");
            int fieldsProcessed = 0;
            foreach (InsertExpression colValPair in insert.ColumnsAndValues)
            {
                // Skip auto-id field.
                if (!colValPair.Column.AutoIncrement)
                {
                    colValPair.Column.RenderColumnName(dbms, output);
                    fieldsProcessed++;
                    if (fieldsProcessed < numberOfFieldsToRender)
                        output.Append(", ");
                }
            }
            output.Append(")");
        }

        private static void AppendValuesForAllFieldsExceptAutoId(InsertStatement insert, DbmsType dbms, StringBuilder output, DbParameterCollection parameters, int numberOfFieldsToRender)
        {
            output.Append(" VALUES (");
            int valuesProcessed = 0;
            foreach (InsertExpression colValPair in insert.ColumnsAndValues)
            {
                // Skip auto-id field.
                if (!colValPair.Column.AutoIncrement)
                {
                    IRenderSql currValueItem = colValPair.ValueExpression;
                    currValueItem.Render(dbms, output, parameters);

                    valuesProcessed++;
                    if (valuesProcessed < numberOfFieldsToRender)
                        output.Append(", ");
                }
            }
            output.Append(")");
        }

        #endregion

        #region Execute.

        /// <summary>Executes the INSERT command. Automatically generates the code that retrieves the new identity for 
        /// the supported databases. DBMS specific code depends on the DBMS property of the used ConnectionProvider.</summary>
        /// <param name="insert">INSERT statement to execute.</param>
        /// <param name="dbms">Target DBMS.</param>
        /// <param name="conn">Connection-transaction context to use.</param>
        /// <param name="lastExecutedCommandInfo">Output parameter: statistic for executed command.</param>
        /// <returns>Automatically generated ID for inserted row, or <b>null</b> if ID is not automatically generated.</returns>
        public object Execute(InsertStatement insert, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            // Renderer and DBMS will compute next ID, insert row and retrieve ID in one trip.
            StringBuilder cmdtxt = new StringBuilder();
            DbParameterCollection parameters = new DbParameterCollection();
            RenderInsert(insert, null, dbms, cmdtxt, parameters);

            string command = cmdtxt.ToString();
            object id;
            lastExecutedCommandInfo = new CommandExecutionStatistics(command);
            if (HasAutoIdField(insert.Table))
            {
                DataTable autoIncrementData = DbUtil.ExecuteQuery(conn, command, parameters, CommandType.Text, null);
                id = autoIncrementData.Rows[0][0];
                if (id == DBNull.Value)
                    id = null;
            }
            else
            {
                DbUtil.ExecuteNonQuery(conn, command, parameters, CommandType.Text);
                id = null;
            }

            lastExecutedCommandInfo.StopTime();
            return id;
        }

        #endregion
    }
}